package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ErpStockInFormEntity;
import com.b2c.entity.ErpStockInFormItemEntity;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.finance.ErpStockInFormItemVo;
import com.b2c.entity.query.ErpStockInItemQuery;
import com.b2c.entity.vo.ErpStockInFormDetailVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 *
 * @author qlp
 * @date 2019-10-09 14:56
 */
@Repository
public class ErpStockInFormRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 获取入库单详情
     *
     * @param id
     * @return
     */
    public ErpStockInFormDetailVo getById(Long id) {
        try {
            String sql = "SELECT a.*,(SELECT SUM(quantity) FROM " + Tables.ErpStockInFormItem + " WHERE formId=a.id) as quantity,ckc.number as checkoutNo,inv.contractNo " +
                    " FROM " + Tables.ErpStockInForm + " a ";
            sql += " LEFT JOIN " + Tables.ErpStockInCheckout + " ckc on ckc.id=a.checkoutId ";
            sql += " LEFT JOIN " + Tables.ErpInvoice + " inv on inv.id=ckc.invoiceId OR inv.id=a.invoiceId";
            sql += " WHERE a.id=? ";

            var form = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ErpStockInFormDetailVo.class), id);
            //查询明细
            String itemSQL = "SELECT fi.*,g.name as goodsName,g.number as goodsNumber,gs.specNumber,gs.color_value,gs.size_value,sl.number as locationNumber,unit.name as unitName " +
                    " FROM " + Tables.ErpStockInFormItem + " fi " +
                    " LEFT JOIN " + Tables.ErpGoods + " g on g.id=fi.goodsId " +
                    " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id=fi.specId " +
                    " LEFT JOIN " + Tables.ErpStockLocation + " sl on sl.id=fi.locationId " +
                    " LEFT JOIN " + Tables.ErpUnit + " unit on unit.id=g.unitId " +
                    " WHERE fi.formId=?";
            var items = jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(ErpStockInFormItemEntity.class), id);
            form.setItems(items);

            return form;
        } catch (Exception e) {
            return null;
        }

    }

    /**
     * 根据验货单获取入库list
     *
     * @param checkoutId
     * @return
     */
    public List<ErpStockInFormEntity> getListByCheckoutId(Long checkoutId) {
        String sql = "SELECT a.*,(SELECT SUM(quantity) FROM " + Tables.ErpStockInFormItem + " WHERE formId=a.id) as quantity  FROM " + Tables.ErpStockInForm + " a WHERE a.checkoutId=?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormEntity.class), checkoutId);
    }

    /**
     * 查询所有入库单
     *
     * @param pageIndex
     * @param pageSize
     * @param number    单号
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return
     */
    public PagingResponse<ErpStockInFormEntity> getErpStockInList(Integer pageIndex, Integer pageSize,Integer inType, String number, Integer startDate,Integer endDate,Long invoiceId) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS a.*,(SELECT SUM(quantity) FROM " + Tables.ErpStockInFormItem + " WHERE formId=a.id) as quantity" +

                " FROM " + Tables.ErpStockInForm + " a ";
//        sql += " LEFT JOIN " + Tables.ErpStockInCheckout + " ck on ck.id = a.checkoutId ";
//        sql += " LEFT JOIN " + Tables.ErpInvoice + " inv on inv.id = a.invoiceId " ;
        sql += " WHERE 1=1 ";


        List<Object> params = new ArrayList<>();
        if(inType!=null && inType>0){
            sql += " AND a.inType=? ";
            params.add(inType);
        }
        if(invoiceId.longValue()>0){
            //根据采购单id查询
//            sql += " AND inv.id=? ";
            sql += " AND a.invoiceId =? ";
            params.add(invoiceId);
        }else {
            if (StringUtils.isEmpty(number) == false) {
                sql += " AND a.number=? ";
                params.add(number);
            }
            if (StringUtils.isEmpty(startDate) == false && startDate>0) {
                sql += " AND a.stockInTime1 >= ?";
                params.add(startDate);

            }
            if (StringUtils.isEmpty(endDate) == false && endDate>0) {
                sql += " AND a.stockInTime1 <= ?";
                params.add(endDate);
            }
        }
        sql += " ORDER BY a.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormEntity.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    private String getStockInItemListSQLByPurchase(){
        String sql = "SELECT SQL_CALC_FOUND_ROWS fi.*,g.name as goodsName,unit.name as unitName,g.number as goodsNumber,gs.specNumber,gs.color_value,gs.color_image,gs.size_value,sl.number as locationNumber " +
                " ,f.stockInTime,inv.billNo,inv.billDate,inv.contractNo,f.number as stockInNumber,f.stockInUserName,f.stockInTime1,f.id as stockInId,cont.name as contactName" +
                " FROM " + Tables.ErpStockInFormItem + " fi " +
                " LEFT JOIN " + Tables.ErpStockInForm + " f on f.id = fi.formId " +
                // " LEFT JOIN " + Tables.ErpStockInCheckout + " ckc on ckc.id = f.checkoutId " +
                " LEFT JOIN " + Tables.ErpInvoice + " inv on inv.id = f.invoiceId" +
                " LEFT JOIN " + Tables.ErpGoods + " g on g.id = fi.goodsId " +
                " LEFT JOIN " + Tables.ErpUnit + " unit on unit.id = g.unitId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id = fi.specId " +
                " LEFT JOIN " + Tables.ErpStockLocation + " sl on sl.id = fi.locationId " +
                " LEFT JOIN " + Tables.ErpContact + " as cont on cont.id = inv.contactId"+
                " WHERE 1=1 ";
        return sql;
    }

    /**
     * 获取入库数据(采购入库数据)
     * @param pageIndex
     * @param pageSize
     * @param billNo
     * @param contractNo
     * @param stockInNumber
     * @param startTime
     * @param endTime
     * @param inType 入库类型1采购入库2退货入库3盘点入库
     * @return
     */
    @Transactional
    public PagingResponse<ErpStockInFormItemVo> getErpStockInItemListByPurchase(Integer pageIndex, Integer pageSize, String billNo, String contractNo,String stockInNumber,String specNumber, Integer startTime, Integer endTime) {
        //查询明细
        String sql = getStockInItemListSQLByPurchase();
        List<Object> params = new ArrayList<>();

        sql += " AND f.inType=? ";
        params.add(1);
        //条件
        if (StringUtils.isEmpty(billNo) == false) {
            sql += " AND inv.billNo = ? ";
            params.add(billNo);
        }
        if (StringUtils.isEmpty(specNumber) == false) {
            sql += " AND gs.specNumber = ? ";
            params.add(specNumber);
        }
        if (StringUtils.isEmpty(contractNo) == false) {
            sql += " AND inv.contractNo = ? ";
            params.add(contractNo);
        }
        if (StringUtils.isEmpty(stockInNumber) == false) {
            sql += " AND f.number = ? ";
            params.add(stockInNumber);
        }
        if (startTime != null && startTime > 0) {
            sql += " AND inv.createTime >= ? ";
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql += " AND inv.createTime <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY fi.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<ErpStockInFormItemVo> getErpStockInItemListByPurchaseForExcel(String billNo, String contractNo,String stockInNumber, Integer startTime, Integer endTime) {
        String sql = getStockInItemListSQLByPurchase();
        List<Object> params = new ArrayList<>();
        sql += " AND f.inType=? ";
        params.add(1);
        //条件
        if (StringUtils.isEmpty(billNo) == false) {
            sql += " AND inv.billNo = ? ";
            params.add(billNo);
        }
        if (StringUtils.isEmpty(contractNo) == false) {
            sql += " AND inv.contractNo = ? ";
            params.add(contractNo);
        }
        if (StringUtils.isEmpty(stockInNumber) == false) {
            sql += " AND f.number = ? ";
            params.add(stockInNumber);
        }
        if (startTime != null && startTime > 0) {
            sql += " AND inv.createTime >= ? ";
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql += " AND inv.createTime <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY fi.id DESC ";

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
        return list;
    }

    private String getStockInItemListSQLByRefund(){
        String sql = "SELECT SQL_CALC_FOUND_ROWS fi.*,g.name as goodsName,unit.name as unitName," +
                "g.number as goodsNumber,gs.specNumber,gs.color_value,gs.size_value,sl.number as locationNumber " +
                " ,f.stockInTime1,f.number as stockInNumber,f.id as stockInId,ret.contactPerson as contactName " +
                " FROM " + Tables.ErpStockInFormItem + " fi " +
                " LEFT JOIN " + Tables.ErpStockInForm + " f on f.id = fi.formId " +
                " LEFT JOIN "+Tables.ErpOrderReturn +" ret on ret.id = f.invoiceId "+
                " LEFT JOIN " + Tables.ErpGoods + " g on g.id = fi.goodsId " +
                " LEFT JOIN " + Tables.ErpUnit + " unit on unit.id = g.unitId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs on gs.id = fi.specId " +
                " LEFT JOIN " + Tables.ErpStockLocation + " sl on sl.id = fi.locationId " +

                " WHERE 1=1 ";
        return sql;
    }
    /**
     * 查询退货入库数据
     * @param pageIndex
     * @param pageSize
     * @param refundNo
     * @param startTime
     * @param endTime
     * @return
     */
    public PagingResponse<ErpStockInFormItemVo> getErpStockInItemListByRefund(Integer pageIndex, Integer pageSize, String refundNo, Integer startTime, Integer endTime) {
        //查询明细
       String sql = getStockInItemListSQLByRefund();

        List<Object> params = new ArrayList<>();

        sql += " AND f.inType=? ";
        params.add(2);
        //条件
        if (StringUtils.isEmpty(refundNo) == false) {
            sql += " AND ret.order_num = ? ";
            params.add(refundNo);
        }

        if (startTime != null && startTime > 0) {
            sql += " AND f.stockInTime1 >= ? ";
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql += " AND f.stockInTime1 <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY fi.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<ErpStockInFormItemVo> getErpStockInItemListByRefundForExcel(String refundNo, Integer startTime, Integer endTime) {
        //查询明细
        String sql = getStockInItemListSQLByRefund();

        List<Object> params = new ArrayList<>();

        sql += " AND f.inType=? ";
        params.add(2);
        //条件
        if (StringUtils.isEmpty(refundNo) == false) {
            sql += " AND ret.order_num = ? ";
            params.add(refundNo);
        }

        if (startTime != null && startTime > 0) {
            sql += " AND f.stockInTime1 >= ? ";
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql += " AND f.stockInTime1 <= ? ";
            params.add(endTime);
        }

        sql += " ORDER BY fi.id DESC ";
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
        return list;
    }
    /**
     * 查询仓库采购入库数据统计
     * @return
     */
    public PagingResponse<ErpStockInFormItemVo> purchase_in_list_count(ErpStockInItemQuery query) {
        //查询明细
        String sql =  "   SELECT SQL_CALC_FOUND_ROWS sum(fi.quantity) quantity,sum(fi.quantity*invi.price) as purPrice, g.name as goodsName,g.number as goodsNumber,gs.specNumber,gs.color_value,gs.size_value,gs.color_image,fi.specId  \n" +
                "\t FROM  erp_stock_in_form_item   fi " +
                "\t LEFT JOIN  erp_stock_in_form  f on f.id = fi.formId " +
                "\t LEFT JOIN  erp_invoice   inv on inv.id = f.invoiceId " +
                "\t LEFT JOIN  erp_invoice_info   invi on invi.id = fi.itemId " +
                "\t LEFT JOIN  erp_goods_spec  gs on gs.id = fi.specId  " +
                "\t LEFT JOIN  erp_goods  g on g.id = gs.goodsId " +
                "\t WHERE f.inType=1   ";

        List<Object> params = new ArrayList<>();

        sql += "  and f.stockInTime1 >0 ";
        //条件
        if (StringUtils.isEmpty(query.getBillNo()) == false) {
            sql += " AND inv.billNo = ? ";
            params.add(query.getBillNo());
        }
        if (StringUtils.isEmpty(query.getContractNo()) == false) {
            sql += " AND inv.contractNo = ? ";
            params.add(query.getContractNo());
        }
        if (StringUtils.isEmpty(query.getStockInNumber()) == false) {
            sql += " AND f.number = ? ";
            params.add(query.getStockInNumber());
        }
        if(!StringUtils.isEmpty(query.getGoodsNumber())){
            sql += " AND g.number = ? ";
            params.add(query.getGoodsNumber());
        }
        if(!StringUtils.isEmpty(query.getSkuNumber())){
            sql += " AND gs.specNumber = ? ";
            params.add(query.getSkuNumber());
        }
        if (!StringUtils.isEmpty(query.getStartTime()) && query.getStartTime()>0) {
            sql += " AND f.stockInTime1 >= ? ";
            params.add(query.getStartTime());
        }
        if (!StringUtils.isEmpty(query.getEndTime()) && query.getEndTime()>0) {
            sql += " AND f.stockInTime1 <= ? ";
            params.add(query.getEndTime());
        }

        sql += " GROUP BY fi.specId ORDER BY gs.id DESC LIMIT ?,?";
        params.add((query.getPageIndex() - 1) * query.getPageSize());
        params.add(query.getPageSize());
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
        int totalSize=getTotalSize();
        return new PagingResponse<>(query.getPageIndex(), query.getPageSize(), totalSize, list);
    }

    public List<ErpStockInFormItemVo> purchase_in_list_count_export(ErpStockInItemQuery query) {
        //查询明细
        String sql =  "   SELECT SQL_CALC_FOUND_ROWS sum(fi.quantity) quantity,sum(fi.quantity*invi.price) as purPrice, g.name as goodsName,g.number as goodsNumber,gs.specNumber,gs.color_value,gs.size_value,gs.color_image,fi.specId  \n" +
                "\t FROM  erp_stock_in_form_item   fi " +
                "\t LEFT JOIN  erp_stock_in_form  f on f.id = fi.formId " +
                "\t LEFT JOIN  erp_invoice   inv on inv.id = f.invoiceId " +
                "\t LEFT JOIN  erp_invoice_info   invi on invi.id = fi.itemId " +
                "\t LEFT JOIN  erp_goods  g on g.id = fi.goodsId " +
                "\t LEFT JOIN  erp_goods_spec  gs on gs.id = fi.specId  " +
                "\t WHERE f.inType=1   ";

        List<Object> params = new ArrayList<>();

        sql += "  and f.stockInTime1 >0 ";
        //条件
        if (StringUtils.isEmpty(query.getBillNo()) == false) {
            sql += " AND inv.billNo = ? ";
            params.add(query.getBillNo());
        }
        if (StringUtils.isEmpty(query.getContractNo()) == false) {
            sql += " AND inv.contractNo = ? ";
            params.add(query.getContractNo());
        }
        if (StringUtils.isEmpty(query.getStockInNumber()) == false) {
            sql += " AND f.number = ? ";
            params.add(query.getStockInNumber());
        }
        if(!StringUtils.isEmpty(query.getGoodsNumber())){
            sql += " AND g.number = ? ";
            params.add(query.getGoodsNumber());
        }
        if(!StringUtils.isEmpty(query.getSkuNumber())){
            sql += " AND gs.specNumber = ? ";
            params.add(query.getSkuNumber());
        }
        if (!StringUtils.isEmpty(query.getStartTime()) && query.getStartTime()>0) {
            sql += " AND f.stockInTime1 >= ? ";
            params.add(query.getStartTime());
        }
        if (!StringUtils.isEmpty(query.getEndTime()) && query.getEndTime()>0) {
            sql += " AND f.stockInTime1 <= ? ";
            params.add(query.getEndTime());
        }

        sql += " GROUP BY fi.specId ORDER BY fi.id ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class), params.toArray());
    }

    public  List<ErpStockInFormItemVo> purchase_in_ht_export(Long iid){
        //查询明细
        String sql =  "SELECT invi.*,invi.price as purPrice,g.name as goodsName,gs.color_value,gs.size_value,gs.color_image,inv.contractNo,unit.name as unitName  \n" +
                "\t FROM  erp_invoice_info   invi " +
                "\t LEFT JOIN  erp_invoice   inv on inv.id = invi.iid " +
                "\t LEFT JOIN  erp_goods  g on g.id = invi.goodsId " +
                "\t LEFT JOIN  erp_goods_spec  gs on gs.id = invi.specId  " +
                " LEFT JOIN " + Tables.ErpUnit + " unit on unit.id = g.unitId " +
                "\t WHERE invi.iid=?   ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpStockInFormItemVo.class),iid);
    }
}
